﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Collections.Generic;

namespace MvePublicService.KeywordOperations
{
	/*
	 * 日本SGI株式会社
	 * ヨハンソン・ヘンリク
	 * johansson@sgi.co.jp
	 * 
	 * Change Log:
	 * ---------------------------------------------------------------------------
	 * 2009.01.07	Johansson Henrik		Class Created.
	 * 2009.01.09							Updated QuerySegmentByKeyword to always return an XML string.
	 *										Currently the application expect the reply to be an empty
	 *										Xml Document even if the keyword is to long or no matching 
	 *										segments were founds.
	 * 2009.01.13							Changed the QuerySegmentByKeyword to take and pass on the
	 *										users Db Id to the database. This is to enable logging of
	 *										searches in the Db.
	 */

	/// <summary>
	/// This static class provides methods for extracting, 
	/// updating and deleting Keywords in the Database.
	/// Use the static methods in this class to perform  
	/// Keyword related operations on the Database.
	/// </summary>
	/// <remarks>INSERT:Creds</remarks>
	internal static class KeywordManipulator
	{
		/// <summary>
		/// Original Name = QuerySegmentByKeyword
		/// </summary>
		/// <remarks>Done</remarks>
		/// <param name="userDbId">Users DB id. Used for operation Logging.</param>
		/// <param name="keywordToMatch">The Keyword to seach for</param>
		/// <param name="connection">Sql Connection</param>
		/// <returns>The Xml string that contains the response document.</returns>
		internal static string QuerySegmentByKeyword(int userDbId, string keywordToMatch, SqlConnection connection)
		{
			//Build The final result document
			XmlDocument doc = null;
			XmlElement dataRoot = XmlConstructor.FormatResponseDocument(ref doc);
			XmlElement resRoot = doc.CreateElement("segments");
			dataRoot.AppendChild(resRoot);

			string result = string.Empty;

			try
			{

				if (keywordToMatch.Length > 64)
					throw new ArgumentException("Search string is too long. Max characters is 64.");

				List<ClipOperations.Segment> _matchingSegments =
					new List<ClipOperations.Segment>();

				List<int> _segmentIdsToLoad = new List<int>();

				_segmentIdsToLoad =
					ClipOperations.SearchOperations.SearchSegmentIdsByKeyword(
					 userDbId, keywordToMatch, connection);

				//Get each matching segment from the database.
				foreach (int relId in _segmentIdsToLoad)
				{
					ClipOperations.Segment segment =
						ClipOperations.SegmentLoader.LoadSegmentFromDatabase(relId, connection);

					if (segment != null)
						_matchingSegments.Add(segment);
				}

				foreach (ClipOperations.Segment segment in _matchingSegments)
				{
					ClipOperations.Clip clip =
						ClipOperations.ClipLoader.LoadClipFromDatabase(segment.ParentClipId, connection);

					if (clip != null)
					{
						XmlElement clipRoot = XmlConstructor.AddClipNodeToDocument(doc, clip, resRoot);
						XmlElement segmentRoot = XmlConstructor.AddSegmentNodeToClip(doc, segment, clip.OnAirDate, clipRoot);

						XmlElement kwRoot = doc.CreateElement("keywords");
						segmentRoot.AppendChild(kwRoot);

						List<KeywordOperations.Keyword> segmentKeywords =
							KeywordOperations.KeywordLoader.LoadKeywordsForSegmentFromDatabase(
							segment.Id, connection);

						if (segmentKeywords != null)
						{
							foreach (KeywordOperations.Keyword keyword in segmentKeywords)
							{
								XmlConstructor.AddKeywordNodeToSegment(
								doc, keyword, kwRoot);
							}
						}
					}
				}

				result = doc.OuterXml;

			}
			catch (Exception ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = doc.OuterXml;
			}
			return result;

		}//end:QuerySegmentByKeyword

		/// <summary>
		/// Get all keywords registered by the given User DB id.
		/// Original Name = QueryKeywords
		/// </summary>
		/// <remarks>Done</remarks>
		/// <param name="userDbId">User DB id to match</param>
		/// <param name="connection">Sql Connection</param>
		/// <returns>The Xml string that contains the response document.</returns>
		internal static string QueryKeywordsForUser(int userDbId, SqlConnection connection)
		{
			SqlCommand command = new SqlCommand();
			SqlDataReader reader = null;
			command.CommandTimeout = 60;

			XmlDocument doc = null;
			XmlElement dataRoot = XmlConstructor.FormatResponseDocument(ref doc);
			XmlElement dataNodeRoot = doc.CreateElement("keywords");
			dataRoot.AppendChild(dataNodeRoot);

			string result = string.Empty;
			try
			{
				command.Connection = connection;
				command.CommandType = System.Data.CommandType.StoredProcedure;
				command.CommandText = "GetKeywordsByUserId";

				command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;

				reader = command.ExecuteReader();

				if (reader.HasRows)
				{
					if (doc != null)
					{
						while (reader.Read())
						{
							XmlElement kwNodeRoot = doc.CreateElement("keyword");
							dataNodeRoot.AppendChild(kwNodeRoot);

							int id = reader.GetInt32(0);
							string word = reader.GetString(4);

							XmlElement idE = doc.CreateElement("id");
							idE.InnerText = id.ToString();
							kwNodeRoot.AppendChild(idE);

							XmlElement wordE = doc.CreateElement("keyword");
							wordE.InnerText = word;
							kwNodeRoot.AppendChild(wordE);
						}
					}
				}

				result = doc.OuterXml;
			}
			catch (NullReferenceException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = doc.OuterXml;
			}
			catch (SqlException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = doc.OuterXml;
			}
			catch (InvalidOperationException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = doc.OuterXml;
			}
			catch (IndexOutOfRangeException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = doc.OuterXml;
			}
			finally
			{
				command.Dispose();
				if (reader != null)
				{
					reader.Close();
					reader.Dispose();
				}
			}

			return result;
		}//end:QueryKeywordsForUser

		/// <summary>
		/// Add a keyword to a segment.
		/// Original Name = AddKeywordToSegment
		/// This method adds a segment keyword whose member_id is set to null.
		/// As of 01/09/09 the UserDbId is sent to the DB but it has no meaning.
		/// </summary>
		/// <remarks>Done</remarks>
		/// <param name="userDbId">User DB id</param>
		/// <param name="segmentId">Id of the segment the keyword belongs to</param>
		/// <param name="keyword">The keyword to add</param>
		/// <param name="connection">Sql Connection</param>
		/// <returns>True if the keyword was added</returns>
		internal static bool AddKeywordToSegment(int userDbId, int segmentId, string keyword, SqlConnection connection)
		{
			SqlCommand command = new SqlCommand();
			command.CommandTimeout = 60;

			bool result = false;
			try
			{
				command.Connection = connection;
				command.CommandType = System.Data.CommandType.StoredProcedure;
				command.CommandText = "AddSegmentKeyword";

				command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;
				command.Parameters.Add("segmentId", SqlDbType.Int).Value = segmentId;
				command.Parameters.Add("keyword", SqlDbType.VarChar, 64).Value = keyword;

				SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
				returnValue.Direction = ParameterDirection.ReturnValue;
				command.Parameters.Add(returnValue);

				command.ExecuteNonQuery();
				int resultCode = (int)returnValue.Value;

				if (resultCode == 0)
				{
					result = true;
				}
			}
			catch (InvalidOperationException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = false;
			}
			catch (InvalidCastException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = false;
			}
			finally
			{
				command.Dispose();
			}

			return result;
		}//end:AddKeywordToSegment

		/// <summary>
		/// Delete a segment Keyword from the DB.
		/// Original Name = DeleteKeyword
		/// As of 01/09/09 the UserDbId is sent to the DB but it has no meaning.
		/// </summary>
		/// <remarks>Done</remarks>
		/// <param name="userDbId">User DB id</param>
		/// <param name="keywordId">Id of the keyword to delete</param>
		/// <param name="connection">Sql Connection</param>
		/// <returns>True if the keyword was deleted</returns>
		internal static bool DeleteKeywordById(int userDbId, int keywordId, SqlConnection connection)
		{
			SqlCommand command = new SqlCommand();
			command.CommandTimeout = 60;

			bool result = false;
			try
			{
				command.Connection = connection;
				command.CommandType = System.Data.CommandType.StoredProcedure;
				command.CommandText = "DeleteSegmentKeyword";

				command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;
				command.Parameters.Add("keywordId", SqlDbType.Int).Value = keywordId;

				SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
				returnValue.Direction = ParameterDirection.ReturnValue;
				command.Parameters.Add(returnValue);

				command.ExecuteNonQuery();
				int resultCode = (int)returnValue.Value;

				if (resultCode == 0)
				{
					result = true;
				}
			}
			catch (InvalidOperationException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = false;
			}
			catch (InvalidCastException ex)
			{
				MvePublicService.ErrorHandler.LogMessage(ex.Message, false);
				result = false;
			}
			finally
			{
				command.Dispose();
			}
			return result;
		}//end:DeleteKeywordById
	}
}
